package model;

import init.DBManagement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

import model.beans.MinutaBean;

public class Draft {
	
	private static final String STR_DRAFTID   = "draft_id";
	private static String str_format = "yyyy/MM/dd";
	private static SimpleDateFormat sdf      = new SimpleDateFormat(str_format);
	private static final boolean DEBUG =  false;
	
	
	public static ArrayList list(String str_academia){
		ArrayList arrL_retVal = new ArrayList();
		String str_list =
			" SELECT  MIN_TECH_KEY as MIN_TECH_KEY,                                     " +
			"         to_char( FECHA_REUNION, '" + str_format + "' ) AS FECHA_REUNION,  " +
			"         ASUNTO AS ASUNTO,                " +
			"         CERRADA "+
			" FROM ACAMINB                            " + 
			" WHERE ACADEMIA_TK = '"+str_academia +"' " ;
		int int_tk;
		String str_asunto;
		Calendar cal_fechaReunion;
		String isEraseable;
		
		DBManagement database = new DBManagement();
		database.executeQuery( str_list );

		for( int int_i = 0; int_i < database.getNumRows(); int_i++ ){
			Map map_id = new HashMap();
			int_tk = Integer.parseInt( database.getItem(int_i, 0) );
			str_asunto = database.getItem(int_i, 2);
			cal_fechaReunion = Calendar.getInstance();
			try {
				cal_fechaReunion.setTime(sdf.parse(database.getItem(int_i, 1)));
			} catch (ParseException e) {
				throw new RuntimeException(e.getMessage());
			}
			map_id.put(STR_DRAFTID, new Integer(int_tk));			
			isEraseable = new Boolean(isDraftErasable(int_tk)).toString();		
			String str_cerrada =  database.getItem(int_i, 3) ;
			MinutaBean tb = new MinutaBean(int_tk, str_asunto, cal_fechaReunion, map_id, str_cerrada);
			tb.setIsEraseable(isEraseable);
			arrL_retVal.add(tb);
		}
		database = null;
		return arrL_retVal;
	}

    public static int add(Map map_data, String str_idAcademia){
    	
        int int_add;
        
        String str_expediente    = (String)map_data.get("str_expediente");
        String str_asunto        = (String)map_data.get("str_asunto");
        int int_responsable      = ((Integer)map_data.get("int_responsable"))==null?0: ((Integer)map_data.get("int_responsable")).intValue() ;
        String str_lugar         = (String)map_data.get("str_lugar");
        String str_fechaReunion  = (String)map_data.get("str_fechaReunion");
        String str_horaReunion   = (String)map_data.get("str_horaReunion");
        String str_horaInicio    = (String)map_data.get("str_horaInicio");
        String str_horaFin       = (String)map_data.get("str_horaFin");
        String str_ordenDia      = (String)map_data.get("str_ordenDia");
        int int_numeroReunion = ((Integer)map_data.get("int_numeroReunion")).intValue();

        String str_add =
            " INSERT INTO ACAMINB(                                           " +
            "                     ACADEMIA_TK,                               " +
            "                     EXPEDIENTE,                                " +
            "                     ASUNTO,                                    " +
            "                     RESPONSABLE_TK,                            " +
            "                     LUGAR,                                     " +
            "                     FECHA_REUNION,                             " +
            "                     HORA_REUNION,                              " +
            "                     HORA_INICIO,                               " +
            "                     HORA_TERMINA,                              " +
            "                     ORDEN_DEL_DIA,                             " +
            "                     NUMERO_REUNION                             " +
            ")                                                               " +
            " VALUES(                                                        " +
            "                   '"+str_idAcademia+"',                        " +
            "                   '"+str_expediente+"',                        " +
            "                   '"+str_asunto+"',                            " +
            "                   "+int_responsable+",                         " +
            "                   '"+str_lugar+"',                             " +
            "          to_date( '"+str_fechaReunion+"', 'yyyy/MM/dd'),       " +
            "                   '"+str_horaReunion+"',                       " +
            "                   '"+str_horaInicio+"',                        " +
            "                   '"+str_horaFin+"' ,                          " +
            "                   '"+str_ordenDia+"',                          " +
            "                    "+int_numeroReunion+")                      " ;
        DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_add );
        int_add = bol_insertStatus ? 1 : 0;
        database = null;
	        return int_add;
	    }

    
    /**
     * 
     * 
     * @param map_delete
     * @return
     */
    public static int delete(Map map_delete){
        int int_delete = ( (Integer) map_delete.get(STR_DRAFTID) ).intValue();
        String str_delete =
            " DELETE FROM ACAMINB                   " +
            " WHERE MIN_TECH_KEY = "+int_delete+"      ";
        DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_delete );
        int_delete = bol_insertStatus ? 1 : 0;
        database = null;
        return int_delete;
    }
    
    
    
    /**
     * 
     * 
     * @param int_min
     * @return
     * @throws ParseException
     */
    public static MinutaBean edit(int int_min) throws ParseException{

        String str_edit =
            " SELECT  MIN_TECH_KEY as MIN_TECH_KEY,                          " +
            "                     EXPEDIENTE,                                " +
            "                     ASUNTO,                                    " +
            "                     RESPONSABLE_TK,                            " +
            "                     NOMBRE AS NOMBRE_RESPONSABLE,              " +
            "                     LUGAR,                                     " +
            "                     to_char( FECHA_REUNION, '" + str_format + "' ) AS FECHA_REUNION,                             " +
            "                     HORA_REUNION,                              " +
            "                     HORA_INICIO,                               " +
            "                     HORA_TERMINA,                              " +
            "                     ORDEN_DEL_DIA,                             " +
            "                     OBSERVACIONES,                             " +
            "                     to_char( REUNION_PROX, '" + str_format + "' ) AS REUNION_PROX,                              " +
            "                     LUGAR_PROX,                                " +
            "                     HORA_PROX,                                 " +
            "                     to_char( ULTIMA_MOD, '" + str_format + "' ) AS ULTIMA_MOD,                                " +
            "                     NUMERO_REUNION, " +
            "                     CERRADA "+
            " FROM    ACAMINB  " +
            " INNER JOIN BASUSRB ON BASUSRB.USR_TECH_KEY   = ACAMINB.RESPONSABLE_TK " +
            " WHERE MIN_TECH_KEY = "+int_min+"              " ;
        
        if(DEBUG)System.out.println(str_edit);
        
        DBManagement database = new DBManagement();
        database.executeQuery( str_edit );
        database.getNumRows();

        MinutaBean bn_minuta = new MinutaBean();
        
        if( database.getNumRows() == 1 ){

            int int_tk               = Integer.parseInt( database.getItem(0, 0) );
            String str_expediente    = database.getItem(0, 1);
            String str_asunto        = database.getItem(0, 2);
            int int_responsable      = Integer.parseInt( database.getItem(0, 3) );
            String str_responsable   = database.getItem(0, 4);
            String str_lugar         = database.getItem(0, 5);
            String str_fechaReunion  = database.getItem(0, 6);
            String str_horaReunion   = database.getItem(0, 7);
            String str_horaInicio    = database.getItem(0, 8);
            String str_horaFin       = database.getItem(0, 9);
            String str_ordenDia      = database.getItem(0, 10);
            String str_observaciones = database.getItem(0, 11);
            String str_reunionProx   = (String)database.getItem(0, 12);
            String str_lugarProx     = database.getItem(0, 13);
            String str_horaProx      = database.getItem(0, 14);
            String str_ultimaMod     = database.getItem(0, 15);
            String str_numeroReunion = database.getItem(0, 16);
            String str_cerrada  = database.getItem(0, 17);
            
            bn_minuta.setInt_tk(int_tk);
            bn_minuta.setStr_expediente(str_expediente);
            bn_minuta.setStr_asunto(str_asunto);
            bn_minuta.setInt_responsable(int_responsable);
            bn_minuta.setStr_responsable(str_responsable);
            bn_minuta.setStr_lugar(str_lugar);
            bn_minuta.setStr_fechaReunion(str_fechaReunion);
            bn_minuta.setStr_horaReunion(str_horaReunion);
            bn_minuta.setStr_horaInicio(str_horaInicio);
            bn_minuta.setStr_horaFin(str_horaFin);
            bn_minuta.setStr_reunionProx(str_reunionProx);
            bn_minuta.setStr_horaProx(str_horaProx);
            bn_minuta.setStr_ultimaMod(str_ultimaMod);
            bn_minuta.setStr_ordenDia(str_ordenDia);
            bn_minuta.setStr_observaciones(str_observaciones);
            bn_minuta.setStr_LugarProx(str_lugarProx);
            bn_minuta.setStr_numeroReunion(str_numeroReunion);
            bn_minuta.setStr_cerrada(str_cerrada);
        }
        return bn_minuta;
    }

    
    
    /**
     * 
     * @param map_data
     * @return
     */
    public static int save(Map map_data){
    	int int_save;
    	int int_min              = ((Integer)map_data.get("int_min")).intValue();
    	
    	Boolean bol_cerrar = (Boolean)map_data.get("bol_cerrar");
    
        String str_expediente="";
        String str_asunto="";
        int int_responsable=0;
        String str_lugar="";
        String str_fechaReunion="";
        String str_horaReunion="";
        String str_horaInicio="";
        String str_horaFin="";
        String str_ordenDia="";
        int int_numeroReunion=0;
        
        if( bol_cerrar== null){
        	str_expediente    = (String)map_data.get("str_expediente");
            str_asunto        = (String)map_data.get("str_asunto");
            int_responsable      = ((Integer)map_data.get("int_responsable"))==null?0: ((Integer)map_data.get("int_responsable")).intValue() ;
            str_lugar         = (String)map_data.get("str_lugar");
            str_fechaReunion  = (String)map_data.get("str_fechaReunion");
            str_horaReunion   = (String)map_data.get("str_horaReunion");
            str_horaInicio    = (String)map_data.get("str_horaInicio");
            str_horaFin       = (String)map_data.get("str_horaFin");
            str_ordenDia      = (String)map_data.get("str_ordenDia");//int_numeroReunion
            int_numeroReunion = ((Integer)map_data.get("int_numeroReunion")).intValue();
        }
        
        String str_save =
            " UPDATE ACAMINB                                                 " +
            ( bol_cerrar== null ? 
            "               SET EXPEDIENTE = '"+str_expediente+"',           " +
            "                   ASUNTO = '"+str_asunto+"',                   " +
            "                   RESPONSABLE_TK =  "+int_responsable+",       " +
            "                   LUGAR =  '"+str_lugar+"' ,                   " +
            "                   FECHA_REUNION =   to_date('"+str_fechaReunion+"', 'yyyy/MM/dd'),     " +
            "                   HORA_REUNION =  '"+str_horaReunion+"',       " +
            "                   HORA_INICIO =  '"+str_horaInicio+"',         " +
            "                   HORA_TERMINA =  '"+str_horaFin+"',           " +
            "                   ORDEN_DEL_DIA = '"+str_ordenDia+"',          " +
            "                   NUMERO_REUNION = '"+int_numeroReunion+"'     " 
            : 
            "             SET CERRADA = 1 " ) + 
            " WHERE MIN_TECH_KEY = "+int_min+"                               ";
        
        if(DEBUG)System.out.println(str_save);
        
        DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_save );
        int_save = bol_insertStatus ? 1 : 0;
        database = null;
        return int_save;
    }
    
    public static int observationSave(Map map_data, int minuta_id){
    	 int  int_save = 0;
        String str_observaciones    = (String)map_data.get("str_observaciones");
        String str_fechaProxima        = (String)map_data.get("str_fechaProxima");
        String str_lugarProximo         = (String)map_data.get("str_lugarProximo");
        String str_hora  = (String)map_data.get("str_horaProxima");        
        
        String str_save =
            " UPDATE ACAMINB                                                 " +
            "               SET OBSERVACIONES = '"+str_observaciones+"',           " +
            "                   REUNION_PROX = to_date('"+str_fechaProxima+"', 'yyyy/MM/dd'),     " +
            "                   LUGAR_PROX =  '"+str_lugarProximo+"',       " +
            "                   HORA_PROX =  '"+str_hora+"'                    " +         
            " WHERE MIN_TECH_KEY = "+minuta_id+"                               ";
        
        if(DEBUG)System.out.println(str_save);
        
        DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_save );
        int_save = bol_insertStatus ? 1 : 0;
        database = null;
        return int_save;
    }
    
    public static boolean isDraftErasable(int int_minuta){
    	boolean ret_val = false;
    	
    	String str_agreement =
    		" SELECT *        " +
    		" FROM ACAACUR     " +
    		" WHERE MIN_TECH_KEY =  " + int_minuta;
    	
    	String str_document = 
			" SELECT *   " +
			" FROM   ACAMDOR                         " +
			" WHERE  MIN_TECH_KEY = " + int_minuta;
    	
    	String str_assistant =
    		" SELECT *      " +
    		" FROM ACAASIR      "+
    		" WHERE MINUTA_TK =    " + int_minuta;
    	
    	 DBManagement database = new DBManagement();
    	 
    	 database.executeQuery( str_agreement );
    	 if(database.getNumRows() == 0){
	    		database.executeQuery( str_document );
	    		if(database.getNumRows() == 0){
	    				database.executeQuery( str_assistant );
	    				if(database.getNumRows() == 0){
	    						ret_val = true;
	    				}else{
	    						ret_val = false;
	    				}
	    		}else{
	    				ret_val = false;
	    		}    			
    	}else{
    			ret_val = false;
    	}
    	 
    	return ret_val;
    }
    
}
